공통 지원 함수(Oracle, MySQL, PostgreSQL)

✒️ 2025-06-05 18:12 내용 수정

• Oracle, MySQL, PostgreSQL에서 공통으로 사용하는 함수를 정리했다.
  • 다른 DB의 함수가 누락되었을 수 있다.
  • 각 RDBMS 함수 노트에 정리하기 애매한 공통 지원 함수를 정리했다.
    • 실제로 공통되는 함수는 매우 많으며, 단지 카테고리화 및 중복 작성 문제로 인해 여기에 일부 함수를 정리했다.

1. COALESCE

• ANSI SQL 표준 함수로, 인수 목록에서 첫 번째로 NULL이 아닌 값을 반환한다.
  • NULL값 처리에 사용한다.
• 함수에 전달하는 모든 인수는 서로 호환 가능한 데이터 타입이어야 한다.

COALESCE(value1, value2, ...)

-- 급여 중 null값이 있을 경우 0으로 처리
SELECT COALESCE(salary, 0) FROM employees;

2. CAST

• 타입의 형변환을 수행한다.
  • 서로 형변환이 호환되지 않는 경우에는 형변환을 할 수 없다.

CAST(대상 AS 변환한타입)

-- grade라는 속성이 TEXT라고 가정할 때
SELECT (grade AS INTEGER) FROM students;

3. LAG

• 참고 자료 : datacamp Understanding the LAG() Function in SQL
• SQL 윈도우 함수 중 하나로, 현재 행 이전의 행에 있는 데이터를 참조할 수 있다.
• 시간 순서나 순위 순서 기반으로 이전 값과 현재 값을 비교하거나 변화량을 계산할 때 유용하다.
• Oracle은 8i 버전 이상, MySQL은 8.0 버전 이상, PostgreSQL은 8.4 이상 버전에서 지원한다.

LAG(표현식 [, 오프셋 [, 기본값]]) OVER (
  [PARTITION BY 파티션_기준]
  ORDER BY 정렬_기준
)

-- 컬럼 기준으로 사용 시 단순화한 표현
LAG(컬럼이름1) OVER(
	PARTITION BY 컬럼이름
	ORDER BY 컬럼이름2
)

• 표현식 : 이전 행에서 가져올 속성(열)이나 계산식이다.
• 오프셋 : 현재 행에서 몇 행 이전의 데이털르 가져올지 지정하며, 기본값은 1이다.
• 기본값 : 이전 행이 존재하지 않을 경우의 반환값이며, 지정하지 않을 경우 NULL을 반환한다.
• PARTITION BY : 데이터를 그룹으로 나누어 각 그룹 내에 LAG()를 적용하고, 생략한다면 전체 데이터를 하나의 그룹으로 처리한다.
• ORDER BY : 각 파티션 내에서 행의 순서를 지정한다.

예시

• 수업 시간에 진행한 실습용 데이터를 이용하여 LAG 함수를 적용한 예시다.

SELECT history_id, member_id, book_title, borrow_date,
LAG(borrow_date) OVER (
  PARTITION BY member_id
  ORDER BY borrow_date
) AS prev_borrow
FROM borrow_history;

SELECT history_id, member_id, book_title, borrow_date,
borrow_date - LAG(borrow_date) OVER (
	PARTITION BY member_id
	ORDER BY member_id ASC, borrow_date ASC
) AS borrow_term
FROM borrow_history;

4. ROW_NUMBER

• 참고 자료 : Microsoft ROW_NUMBER(Transact-SQL)
• 결과로 나온 집합의 행에 순서를 순차적으로 부여한다.
• RANK처럼 순위 매기기에 사용하거나 페이징 처리 등에 사용한다.

ROW_NUMBER() OVER (
	[PARTITION BY 표현식, ...]
	ORDER BY 컬럼이름 정렬순서
)

SELECT
	ROW_NUMBER() OVER (ORDER BY register_date DESC) as row_num,
	username
FROM users;